﻿Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlServerCe

''' <summary>
''' Microsoft SQL Server CE 4.0
''' </summary>
''' <remarks>
''' References System.Data.SqlServerCe.dll
''' Starting with version 4.0, supports GetSchema like all other providers and removed a few miscellaneous incompatibilities with SqlServer
''' Remaining Limitations: 
''' - file format has been changed from 3.5; see CreateConnection for work-around
''' - does not support setting Command.Timeout to anything other than zero; CommandTimeout property in dbBase enforces this
''' - does not support VARCHAR (must use NVARCHAR); GetFieldList in dbBase enforces this
''' </remarks>
Public Class clsDBSQLServerCE
    Inherits clsDB

    ''' <summary>
    ''' Return a copy of the current generic connection
    ''' </summary>
    ''' <returns>Clone of current instance</returns>
    Public Overrides Function Clone() As clsDB
        Dim db As clsDB = New clsDBSQLServerCE
        MyBase.CloneSelf(db)
        Return db
    End Function

    ''' <summary>
    ''' Provider-independent access to CommandBuilder object
    ''' </summary>
    ''' <param name="da">Data adapter</param>
    ''' <returns>CommandBuilder object specific to provider type</returns>
    Public Overrides Function CommandBuilder(ByVal da As dbDataAdapter) As dbCommandBuilder
        Return New SqlCeCommandBuilder(da)
    End Function

    ''' <summary>
    ''' Return a data adapter for the current connection containing data from sql string
    ''' </summary>
    ''' <param name="SqlString">standard sql string</param>
    ''' <returns>Data Adapter</returns>
    Public Overrides Function DataAdapter(ByVal SqlString As String) As DbDataAdapter
        If Connection Is Nothing Then Return Nothing
        Return New SqlCeDataAdapter(SqlString, CType(Connection, SqlCeConnection))
    End Function

    ''' <summary>
    ''' Create a parameter (used to speed up queries and updates); value is initialized to dbnull
    ''' </summary>
    ''' <param name="ParameterName">name of parameter (usually field name)</param>
    ''' <param name="DataType">need to work on this to standarize</param>
    ''' <param name="Size">size if appropriate (strings)</param>
    ''' <returns>Data Parameter</returns>
    Public Overrides Function Parameter(ByRef Command As DbCommand, ByVal ParameterName As String, ByVal DataType As System.Type, Optional ByVal Size As Int16 = 0) As DbParameter
        If Connection Is Nothing Then Return Nothing
        Dim param As SqlServerCe.SqlCeParameter
        Dim SqlType As SqlDbType
        If DataType Is GetType(String) Then
            SqlType = SqlDbType.NVarChar
        ElseIf DataType Is GetType(Date) Then
            SqlType = SqlDbType.DateTime
        ElseIf DataType Is GetType(Double) Then
            SqlType = SqlDbType.Float
        ElseIf DataType Is GetType(Integer) Then
            SqlType = SqlDbType.Int
        ElseIf DataType Is GetType(Boolean) Then
            SqlType = SqlDbType.Bit
        ElseIf DataType Is GetType(Short) Then
            SqlType = SqlDbType.SmallInt
        Else
            Throw New ApplicationException("Invalid type passed to Parameter")
            Return Nothing
        End If
        'If Size > 0 Then
        '    param = New SqlClient.SqlParameter(ParameterName, SqlType, Size)
        'Else
        '    param = New SqlClient.SqlParameter(ParameterName, SqlType)
        'End If
        'param.Value = DBNull.Value
        param = Command.CreateParameter
        With param
            .ParameterName = ParameterName
            .SqlDbType = SqlType
            If Size > 0 Then .Size = Size
            .Value = DBNull.Value
        End With
        Return param
    End Function

    ''' <summary>
    ''' Creates a provider-specific connection object given previously specified properties as required (DataSource, DatabaseName, UserName, Password, etc.)
    ''' Note that the connection is normally automatically created when you instantiate the class
    ''' </summary>
    Public Overrides Sub CreateConnection()
        Try
            Dim csb As New SqlServerCe.SqlCeConnectionStringBuilder
            With csb
                .DataSource = DataSource
                .Password = Password
                Connection = New SqlServerCe.SqlCeConnection(.ConnectionString & ";Max Database Size = 4000")
                'SqlServerCE 4.0 changed the file format, so try to open the database here and if needs to upgrade, do it here too.
                Try
                    Connection.Open()
                Catch ex As SqlCeInvalidDatabaseFormatException
                    Dim sqlCeEngine As New SqlCeEngine(.ConnectionString)
                    sqlCeEngine.Upgrade()
                    sqlCeEngine.Dispose()
                Catch ex As SqlCeException
                    If ex.Message.Contains("cannot be found") Then
                        Exit Sub
                    Else
                        Throw
                    End If
                Finally
                    Connection.Close()
                End Try
            End With
        Catch ex As Exception
            Throw
            Connection = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Creates new database on the provider (uses supervisor access)
    ''' </summary>
    ''' <param name="DatabaseName">Desired name of new database</param>
    ''' <returns>True if successful</returns>
    Public Overrides Function CreateDatabase(ByRef DatabaseName As String) As Boolean
        Dim csb As New SqlServerCe.SqlCeConnectionStringBuilder
        With csb
            .DataSource = DataSource
            .Password = Password
            Try
                Dim sqlCeEngine As New SqlCeEngine(.ConnectionString)
                sqlCeEngine.CreateDatabase()
                Return True
            Catch ex As Exception
                Throw New ApplicationException("Unable to create SQL Server CE database: " & DatabaseName, ex)
                Return False
            End Try
        End With
    End Function

    ' ''' <summary>
    ' ''' Return a list of columns for this table (all fields are converted to UPPERCASE)
    ' ''' </summary>
    ' ''' <param name="TableName">Name of table</param> 
    ' ''' <returns></returns>
    ' ''' <remarks>Allow for possibility that database name is prepended to tablename</remarks>
    'Public Overrides Function GetColumnList(ByVal TableName As String) As Generic.List(Of String)
    '    Try
    '        If Not OpenConnection() Then Return Nothing

    '        Dim col As New Generic.List(Of String)

    '        Dim tblName As String = RemoveBrackets(TableName)
    '        Dim dr As DbDataReader = ExecuteReader(String.Format("SELECT column_name FROM information_schema.columns WHERE table_name LIKE '{0}'", tblName))
    '        While dr IsNot Nothing AndAlso dr.Read
    '            col.Add(dr.GetString(0).ToUpper)
    '        End While
    '        dr.Close()
    '        Return col
    '    Catch ex As Exception
    '        Return New Generic.List(Of String) 'return empty list to avoid later errors
    '    Finally

    '        'DbConnection.Close()
    '    End Try
    'End Function

    ' ''' <summary>
    ' ''' Return list of indexed column for this table (all fields are converted to UPPERCASE)
    ' ''' </summary>
    ' ''' <param name="TableName">Name of table</param>
    ' ''' <returns>List of indexed fields (not including RecID) or Nothing if error occurred</returns>
    ' ''' <remarks>Allow for possibility that database name is prepended to tablename</remarks>
    'Public Overrides Function GetIndexList(ByVal TableName As String) As Generic.List(Of String)
    '    Try
    '        If Not OpenConnection() Then Return Nothing

    '        Dim lstIndex As New Generic.List(Of String)
    '        Dim tblName As String = RemoveBrackets(TableName)
    '        Dim dr As DbDataReader = ExecuteReader(String.Format("SELECT column_name FROM information_schema.indexes WHERE table_name LIKE '{0}'", tblName))
    '        While dr IsNot Nothing AndAlso dr.Read
    '            lstIndex.Add(dr.GetString(0).ToUpper)
    '        End While
    '        dr.Close()
    '        Return lstIndex
    '    Catch ex As Exception
    '        Throw
    '        Return Nothing
    '    Finally
    '        'DbConnection.Close()
    '    End Try
    'End Function

    ' ''' <summary>
    ' ''' Return a list of tables or views for this project from both project database and temp database
    ' ''' </summary>
    ' ''' <returns>List of table or view names</returns>
    'Private Function GetTable_ViewList(ByVal IsTable As Boolean) As Generic.List(Of String)
    '    Try
    '        If Not OpenConnection() Then Return Nothing

    '        Dim lst As New Generic.List(Of String)
    '        Dim dr As DbDataReader = ExecuteReader("SELECT table_name,table_type FROM information_schema.tables")
    '        While dr IsNot Nothing AndAlso dr.Read
    '            Dim table_name As String = Proper(dr.GetString(0))
    '            Dim table_type As String = dr.GetString(1)
    '            lst.Add(table_name)
    '        End While
    '        dr.Close()
    '        Return lst
    '    Catch ex As Exception
    '        Throw
    '        Return Nothing
    '    End Try
    'End Function

    ' ''' <summary>
    ' ''' Return a list of  tables for this project
    ' ''' </summary>
    ' ''' <returns>List of table names</returns>
    'Public Overrides Function GetTableList() As Generic.List(Of String)
    '    Return GetTable_ViewList(True)
    'End Function

    ' ''' <summary>
    ' ''' Return a list of views for this project
    ' ''' </summary>
    ' ''' <returns>List of table names</returns>
    'Public Overrides Function GetViewList() As Generic.List(Of String)
    '    Return GetTable_ViewList(False)
    'End Function

    'Private Function Table_ViewExists(ByVal Name As String, ByVal IsTable As Boolean) As Boolean
    '    Dim tblName As String = RemoveBrackets(Name)
    '    Dim ret As Integer = ExecuteScalar("SELECT COUNT(*) FROM information_schema.tables WHERE table_name LIKE '{0}' AND table_type LIKE '{1}'", tblName, IIf(IsTable, "Table", "View"))
    '    Return (ret > 0)
    'End Function

    ' ''' <summary>
    ' ''' Determine if table exists in the database
    ' ''' </summary>
    ' ''' <param name="TableName">Name of table</param>
    ' ''' <returns>True if exists</returns>
    ' ''' <remarks>Override for Paradox</remarks>
    'Public Overrides Function TableExists(ByVal TableName As String) As Boolean
    '    Return Table_ViewExists(TableName, True)
    'End Function

    ' ''' <summary>
    ' ''' Determine if view exists in the database
    ' ''' </summary>
    ' ''' <param name="ViewName">Name of view</param>
    ' ''' <returns>True if exists</returns>
    'Public Overrides Function ViewExists(ByVal ViewName As String) As Boolean
    '    Return Table_ViewExists(ViewName, False)
    'End Function

    ' ''' <summary>
    ' ''' Return a list of reserved words for this provider
    ' ''' </summary>
    'Public Overrides Function GetReservedWords() As Generic.List(Of String)
    '    Dim col As New Generic.List(Of String)
    '    col.AddRange(New String() {"@@IDENTITY", "ADD", "ALL", "ALTER", "AND", "ANY", "AS", "ASC", "AUTHORIZATION", "AVG", "BACKUP", "BEGIN", "BETWEEN", "BREAK", "BROWSE", "BULK", "BY", "CASCADE", "CASE", "CHECK", "CHECKPOINT", "CLOSE", "CLUSTERED", "COALESCE", "COLLATE", "COLUMN", "COMMIT", "COMPUTE", "CONSTRAINT", "CONTAINS", "CONTAINSTABLE", "CONTINUE", "CONVERT", "COUNT", "CREATE", "CROSS", "CURRENT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "CURSOR", "DATABASE", "DATABASEPASSWORD", "DATEADD", "DATEDIFF", "DATENAME", "DATEPART", "DBCC", "DEALLOCATE", "DECLARE", "DEFAULT", "DELETE", "DENY", "DESC", "DISK", "DISTINCT", "DISTRIBUTED", "DOUBLE", "DROP", "DUMP", "ELSE", "ENCRYPTION", "END", "ERRLVL", "ESCAPE", "EXCEPT", "EXEC", "EXECUTE", "EXISTS", "EXIT", "EXPRESSION", "FETCH", "FILE", "FILLFACTOR", "FOR", "FOREIGN", "FREETEXT", "FREETEXTTABLE", "FROM", "FULL", "FUNCTION", "GOTO", "GRANT", "GROUP", "HAVING", "HOLDLOCK", "IDENTITY", "IDENTITY_INSERT", "IDENTITYCOL", "IF", "IN", "INDEX", "INNER", "INSERT", "INTERSECT", "INTO", "IS", "JOIN", "KEY", "KILL", "LEFT", "LIKE", "LINENO", "LOAD", "MAX", "MIN", "NATIONAL", "NOCHECK", "NONCLUSTERED", "NOT", "NULL", "NULLIF", "OF", "OFF", "OFFSETS", "ON", "OPEN", "OPENDATASOURCE", "OPENQUERY", "OPENROWSET", "OPENXML", "OPTION", "OR", "ORDER", "OUTER", "OVER", "PERCENT", "PLAN", "PRECISION", "PRIMARY", "PRINT", "PROC", "PROCEDURE", "PUBLIC", "RAISERROR", "READ", "READTEXT", "RECONFIGURE", "REFERENCES", "REPLICATION", "RESTORE", "RESTRICT", "RETURN", "REVOKE", "RIGHT", "ROLLBACK", "ROWCOUNT", "ROWGUIDCOL", "RULE", "SAVE", "SCHEMA", "SELECT", "SESSION_USER", "SET", "SETUSER", "SHUTDOWN", "SOME", "STATISTICS", "SUM", "SYSTEM_USER", "TABLE", "TEXTSIZE", "THEN", "TO", "TOP", "TRAN", "TRANSACTION", "TRIGGER", "TRUNCATE", "TSEQUAL", "UNION", "UNIQUE", "UPDATE", "UPDATETEXT", "USE", "USER", "VALUES", "VARYING", "VIEW", "WAITFOR", "WHEN", "WHERE", "WHILE", "WITH", "WRITETEXT"})
    '    Return col
    'End Function

End Class
